﻿using System.Web;
using System.IO;
//3.引入操作Excel的命名控件:XSSF:适合2007及其以上的office版本，低版本使用HSSF
using NPOI.XSSF.UserModel;
using DAL;
using NPOI.SS.UserModel;
using System.Text;

namespace StudentManagerPro.Handlers
{
    /// <summary>
    /// ExportExcel 的摘要说明
    /// </summary>
    public class ExportExcel : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            //1.设置输出类型
            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //2.将Http头添加到输出流，设置默认导出文件名
            string fliename = HttpUtility.UrlEncode("StuScore.xlsx", Encoding.UTF8);
            context.Response.AddHeader("Content-Disposition", "attachment;filename=" + fliename);
            //context.Response.AppendHeader("Content-Disposition",
            //     "attachment;filename=" + HttpUtility.UrlEncode("StuScore.xlsx", Encoding.UTF8));
            //4.创建Excel工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            //5.在工作簿中创建工作表
            ISheet sheet = workbook.CreateSheet("学员成绩表");
            //6.创建工作表的列标题
            IRow rowHeader = sheet.CreateRow(0);
            //DocumentSummaryInformation
            rowHeader.CreateCell(0, CellType.String).SetCellValue("学号");
            rowHeader.CreateCell(1, CellType.String).SetCellValue("姓名");
            rowHeader.CreateCell(2, CellType.String).SetCellValue("性别");
            rowHeader.CreateCell(3, CellType.String).SetCellValue("班级");
            rowHeader.CreateCell(4, CellType.String).SetCellValue("C#成绩");
            rowHeader.CreateCell(5, CellType.String).SetCellValue("数据库成绩");

            //查询数据：
            var className = context.Request.Params["className"].ToString();
            var scoreList = new ScoreService().GetScoreList(className);
            //7.循环将数据写入Excel表格
            for (int i = 0; i < scoreList.Count; i++)
            {
                IRow rowContent = sheet.CreateRow(i+1);
                rowContent.CreateCell(0, CellType.Numeric).SetCellValue(scoreList[i].StudentId);
                rowContent.CreateCell(1, CellType.String).SetCellValue(scoreList[i].StudentName);
                rowContent.CreateCell(2, CellType.String).SetCellValue(scoreList[i].Gender);
                rowContent.CreateCell(3, CellType.String).SetCellValue(scoreList[i].ClassName);
                rowContent.CreateCell(4, CellType.Numeric).SetCellValue(scoreList[i].CSharp);
                rowContent.CreateCell(5, CellType.Numeric).SetCellValue(scoreList[i].SQLServerDB);
            }

            MemoryStream ms = new MemoryStream();
            workbook.Write(ms); 
            ms.Close();
            ms.Dispose();
            workbook.Clear();
            //此方法输出的文件名是正确的
            context.Response.BinaryWrite(ms.ToArray());
            context.Response.End();
            //System.Web.HttpContext.Current.Response.End();
            ////8.将输出流写入到Execl工作部
            //workbook.Write(context.Response.OutputStream);//此方法输出的文件名不对




        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}